{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "Licensed to the Apache Software Foundation (ASF) under one\n",
    "or more contributor license agreements.  See the NOTICE file\n",
    "distributed with this work for additional information\n",
    "regarding copyright ownership.  The ASF licenses this file\n",
    "to you under the Apache License, Version 2.0 (the\n",
    "\"License\"); you may not use this file except in compliance\n",
    "with the License.  You may obtain a copy of the License at\n",
    "  http://www.apache.org/licenses/LICENSE-2.0\n",
    "Unless required by applicable law or agreed to in writing,\n",
    "software distributed under the License is distributed on an\n",
    "\"AS IS\" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY\n",
    "KIND, either express or implied.  See the License for the\n",
    "specific language governing permissions and limitations\n",
    "under the License.\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "import geopandas as gpd\n",
    "from pyspark.sql import SparkSession\n",
    "\n",
    "from sedona.spark import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ":: loading settings :: url = jar:file:/home/jovyan/spark-3.4.2-bin-hadoop3/jars/ivy-2.5.1.jar!/org/apache/ivy/core/settings/ivysettings.xml\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Ivy Default Cache set to: /home/jovyan/.ivy2/cache\n",
      "The jars for the packages stored in: /home/jovyan/.ivy2/jars\n",
      "org.apache.sedona#sedona-spark-3.4_2.12 added as a dependency\n",
      "org.datasyslab#geotools-wrapper added as a dependency\n",
      "uk.co.gresearch.spark#spark-extension_2.12 added as a dependency\n",
      ":: resolving dependencies :: org.apache.spark#spark-submit-parent-713da225-9e04-45e3-9233-485d4b212b3c;1.0\n",
      "\tconfs: [default]\n",
      "\tfound org.apache.sedona#sedona-spark-3.4_2.12;1.6.0 in central\n",
      "\tfound org.apache.sedona#sedona-common;1.6.0 in central\n",
      "\tfound org.apache.commons#commons-math3;3.6.1 in central\n",
      "\tfound org.locationtech.jts#jts-core;1.19.0 in central\n",
      "\tfound org.wololo#jts2geojson;0.16.1 in central\n",
      "\tfound org.locationtech.spatial4j#spatial4j;0.8 in central\n",
      "\tfound com.google.geometry#s2-geometry;2.0.0 in central\n",
      "\tfound com.google.guava#guava;25.1-jre in central\n",
      "\tfound com.google.code.findbugs#jsr305;3.0.2 in central\n",
      "\tfound org.checkerframework#checker-qual;2.0.0 in central\n",
      "\tfound com.google.errorprone#error_prone_annotations;2.1.3 in central\n",
      "\tfound com.google.j2objc#j2objc-annotations;1.1 in central\n",
      "\tfound org.codehaus.mojo#animal-sniffer-annotations;1.14 in central\n",
      "\tfound com.uber#h3;4.1.1 in central\n",
      "\tfound net.sf.geographiclib#GeographicLib-Java;1.52 in central\n",
      "\tfound com.github.ben-manes.caffeine#caffeine;2.9.2 in central\n",
      "\tfound org.checkerframework#checker-qual;3.10.0 in central\n",
      "\tfound com.google.errorprone#error_prone_annotations;2.5.1 in central\n",
      "\tfound org.apache.sedona#sedona-spark-common-3.4_2.12;1.6.0 in central\n",
      "\tfound commons-lang#commons-lang;2.6 in central\n",
      "\tfound org.scala-lang.modules#scala-collection-compat_2.12;2.5.0 in central\n",
      "\tfound org.beryx#awt-color-factory;1.0.0 in central\n",
      "\tfound org.datasyslab#geotools-wrapper;1.6.0-28.2 in central\n",
      "\tfound uk.co.gresearch.spark#spark-extension_2.12;2.11.0-3.4 in central\n",
      "\tfound com.github.scopt#scopt_2.12;4.1.0 in central\n",
      ":: resolution report :: resolve 2280ms :: artifacts dl 96ms\n",
      "\t:: modules in use:\n",
      "\tcom.github.ben-manes.caffeine#caffeine;2.9.2 from central in [default]\n",
      "\tcom.github.scopt#scopt_2.12;4.1.0 from central in [default]\n",
      "\tcom.google.code.findbugs#jsr305;3.0.2 from central in [default]\n",
      "\tcom.google.errorprone#error_prone_annotations;2.5.1 from central in [default]\n",
      "\tcom.google.geometry#s2-geometry;2.0.0 from central in [default]\n",
      "\tcom.google.guava#guava;25.1-jre from central in [default]\n",
      "\tcom.google.j2objc#j2objc-annotations;1.1 from central in [default]\n",
      "\tcom.uber#h3;4.1.1 from central in [default]\n",
      "\tcommons-lang#commons-lang;2.6 from central in [default]\n",
      "\tnet.sf.geographiclib#GeographicLib-Java;1.52 from central in [default]\n",
      "\torg.apache.commons#commons-math3;3.6.1 from central in [default]\n",
      "\torg.apache.sedona#sedona-common;1.6.0 from central in [default]\n",
      "\torg.apache.sedona#sedona-spark-3.4_2.12;1.6.0 from central in [default]\n",
      "\torg.apache.sedona#sedona-spark-common-3.4_2.12;1.6.0 from central in [default]\n",
      "\torg.beryx#awt-color-factory;1.0.0 from central in [default]\n",
      "\torg.checkerframework#checker-qual;3.10.0 from central in [default]\n",
      "\torg.codehaus.mojo#animal-sniffer-annotations;1.14 from central in [default]\n",
      "\torg.datasyslab#geotools-wrapper;1.6.0-28.2 from central in [default]\n",
      "\torg.locationtech.jts#jts-core;1.19.0 from central in [default]\n",
      "\torg.locationtech.spatial4j#spatial4j;0.8 from central in [default]\n",
      "\torg.scala-lang.modules#scala-collection-compat_2.12;2.5.0 from central in [default]\n",
      "\torg.wololo#jts2geojson;0.16.1 from central in [default]\n",
      "\tuk.co.gresearch.spark#spark-extension_2.12;2.11.0-3.4 from central in [default]\n",
      "\t:: evicted modules:\n",
      "\torg.checkerframework#checker-qual;2.0.0 by [org.checkerframework#checker-qual;3.10.0] in [default]\n",
      "\tcom.google.errorprone#error_prone_annotations;2.1.3 by [com.google.errorprone#error_prone_annotations;2.5.1] in [default]\n",
      "\t---------------------------------------------------------------------\n",
      "\t|                  |            modules            ||   artifacts   |\n",
      "\t|       conf       | number| search|dwnlded|evicted|| number|dwnlded|\n",
      "\t---------------------------------------------------------------------\n",
      "\t|      default     |   25  |   0   |   0   |   2   ||   23  |   0   |\n",
      "\t---------------------------------------------------------------------\n",
      ":: retrieving :: org.apache.spark#spark-submit-parent-713da225-9e04-45e3-9233-485d4b212b3c\n",
      "\tconfs: [default]\n",
      "\t0 artifacts copied, 23 already retrieved (0kB/88ms)\n",
      "24/05/22 18:06:15 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable\n",
      "Setting default log level to \"WARN\".\n",
      "To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "config = (\n",
    "    SedonaContext.builder()\n",
    "    .config(\n",
    "        \"spark.jars.packages\",\n",
    "        \"uk.co.gresearch.spark:spark-extension_2.13:2.14.2-4.0\",\n",
    "    )\n",
    "    .master(\"spark://localhost:7077\")\n",
    "    .getOrCreate()\n",
    ")\n",
    "\n",
    "sedona = SedonaContext.create(config)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Geometry Constructors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ST_Point"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Stage 4:>                                                          (0 + 1) / 1]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------+\n",
      "|     arealandmark|\n",
      "+-----------------+\n",
      "|POINT (1.1 101.1)|\n",
      "|POINT (2.1 102.1)|\n",
      "|POINT (3.1 103.1)|\n",
      "|POINT (4.1 104.1)|\n",
      "|POINT (5.1 105.1)|\n",
      "+-----------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "point_csv_df = (\n",
    "    sedona.read.format(\"csv\")\n",
    "    .option(\"delimiter\", \",\")\n",
    "    .option(\"header\", \"false\")\n",
    "    .load(\"data/testpoint.csv\")\n",
    ")\n",
    "\n",
    "point_csv_df.createOrReplaceTempView(\"pointtable\")\n",
    "\n",
    "point_df = sedona.sql(\n",
    "    \"select ST_Point(cast(pointtable._c0 as Decimal(24,20)), cast(pointtable._c1 as Decimal(24,20))) as arealandmark from pointtable\"\n",
    ")\n",
    "point_df.show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ST_GeomFromText"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------------+--------------------+\n",
      "|            name|         countyshape|\n",
      "+----------------+--------------------+\n",
      "|   Cuming County|POLYGON ((-97.019...|\n",
      "|Wahkiakum County|POLYGON ((-123.43...|\n",
      "|  De Baca County|POLYGON ((-104.56...|\n",
      "|Lancaster County|POLYGON ((-96.910...|\n",
      "| Nuckolls County|POLYGON ((-98.273...|\n",
      "+----------------+--------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "polygon_wkt_df = (\n",
    "    sedona.read.format(\"csv\")\n",
    "    .option(\"delimiter\", \"\\t\")\n",
    "    .option(\"header\", \"false\")\n",
    "    .load(\"data/county_small.tsv\")\n",
    ")\n",
    "\n",
    "polygon_wkt_df.createOrReplaceTempView(\"polygontable\")\n",
    "polygon_df = sedona.sql(\n",
    "    \"select polygontable._c6 as name, ST_GeomFromText(polygontable._c0) as countyshape from polygontable\"\n",
    ")\n",
    "polygon_df.show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ST_GeomFromWKB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------------+--------------------+\n",
      "|            name|         countyshape|\n",
      "+----------------+--------------------+\n",
      "|   Cuming County|POLYGON ((-97.019...|\n",
      "|Wahkiakum County|POLYGON ((-123.43...|\n",
      "|  De Baca County|POLYGON ((-104.56...|\n",
      "|Lancaster County|POLYGON ((-96.910...|\n",
      "| Nuckolls County|POLYGON ((-98.273...|\n",
      "+----------------+--------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "polygon_wkb_df = (\n",
    "    sedona.read.format(\"csv\")\n",
    "    .option(\"delimiter\", \"\\t\")\n",
    "    .option(\"header\", \"false\")\n",
    "    .load(\"data/county_small_wkb.tsv\")\n",
    ")\n",
    "\n",
    "polygon_wkb_df.createOrReplaceTempView(\"polygontable\")\n",
    "polygon_df = sedona.sql(\n",
    "    \"select polygontable._c6 as name, ST_GeomFromWKB(polygontable._c0) as countyshape from polygontable\"\n",
    ")\n",
    "polygon_df.show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ST_GeomFromGeoJSON"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+\n",
      "|         countyshape|\n",
      "+--------------------+\n",
      "|POLYGON ((-87.621...|\n",
      "|POLYGON ((-85.719...|\n",
      "|POLYGON ((-86.000...|\n",
      "|POLYGON ((-86.574...|\n",
      "|POLYGON ((-85.382...|\n",
      "+--------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "polygon_json_df = (\n",
    "    sedona.read.format(\"csv\")\n",
    "    .option(\"delimiter\", \"\\t\")\n",
    "    .option(\"header\", \"false\")\n",
    "    .load(\"data/testPolygon.json\")\n",
    ")\n",
    "\n",
    "polygon_json_df.createOrReplaceTempView(\"polygontable\")\n",
    "polygon_df = sedona.sql(\n",
    "    \"select ST_GeomFromGeoJSON(polygontable._c0) as countyshape from polygontable\"\n",
    ")\n",
    "polygon_df.show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Spatial Operations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Spatial Join - Distance Join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "== Physical Plan ==\n",
      "BroadcastIndexJoin pointshape2#257: geometry, LeftSide, LeftSide, Inner, INTERSECTS, ( **org.apache.spark.sql.sedona_sql.expressions.ST_Distance**   < 2.0) ST_INTERSECTS(pointshape1#232, pointshape2#257)\n",
      ":- SpatialIndex pointshape1#232: geometry, RTREE, false, false, 2.0\n",
      ":  +- Project [ **org.apache.spark.sql.sedona_sql.expressions.ST_Point**   AS pointshape1#232, abc AS name1#233]\n",
      ":     +- FileScan csv [_c0#228,_c1#229] Batched: false, DataFilters: [], Format: CSV, Location: InMemoryFileIndex(1 paths)[file:/home/jovyan/docs/usecases/data/testpoint.csv], PartitionFilters: [], PushedFilters: [], ReadSchema: struct<_c0:string,_c1:string>\n",
      "+- Project [ **org.apache.spark.sql.sedona_sql.expressions.ST_Point**   AS pointshape2#257, def AS name2#258]\n",
      "   +- FileScan csv [_c0#253,_c1#254] Batched: false, DataFilters: [], Format: CSV, Location: InMemoryFileIndex(1 paths)[file:/home/jovyan/docs/usecases/data/testpoint.csv], PartitionFilters: [], PushedFilters: [], ReadSchema: struct<_c0:string,_c1:string>\n",
      "\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------+-----+-----------------+-----+\n",
      "|      pointshape1|name1|      pointshape2|name2|\n",
      "+-----------------+-----+-----------------+-----+\n",
      "|POINT (1.1 101.1)|  abc|POINT (1.1 101.1)|  def|\n",
      "|POINT (2.1 102.1)|  abc|POINT (1.1 101.1)|  def|\n",
      "|POINT (1.1 101.1)|  abc|POINT (2.1 102.1)|  def|\n",
      "|POINT (2.1 102.1)|  abc|POINT (2.1 102.1)|  def|\n",
      "|POINT (3.1 103.1)|  abc|POINT (2.1 102.1)|  def|\n",
      "+-----------------+-----+-----------------+-----+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "point_csv_df_1 = (\n",
    "    sedona.read.format(\"csv\")\n",
    "    .option(\"delimiter\", \",\")\n",
    "    .option(\"header\", \"false\")\n",
    "    .load(\"data/testpoint.csv\")\n",
    ")\n",
    "\n",
    "point_csv_df_1.createOrReplaceTempView(\"pointtable\")\n",
    "\n",
    "point_df1 = sedona.sql(\n",
    "    \"SELECT ST_Point(cast(pointtable._c0 as Decimal(24,20)),cast(pointtable._c1 as Decimal(24,20))) as pointshape1, 'abc' as name1 from pointtable\"\n",
    ")\n",
    "point_df1.createOrReplaceTempView(\"pointdf1\")\n",
    "\n",
    "point_csv_df2 = (\n",
    "    sedona.read.format(\"csv\")\n",
    "    .option(\"delimiter\", \",\")\n",
    "    .option(\"header\", \"false\")\n",
    "    .load(\"data/testpoint.csv\")\n",
    ")\n",
    "\n",
    "point_csv_df2.createOrReplaceTempView(\"pointtable\")\n",
    "point_df2 = sedona.sql(\n",
    "    \"select ST_Point(cast(pointtable._c0 as Decimal(24,20)),cast(pointtable._c1 as Decimal(24,20))) as pointshape2, 'def' as name2 from pointtable\"\n",
    ")\n",
    "point_df2.createOrReplaceTempView(\"pointdf2\")\n",
    "\n",
    "distance_join_df = sedona.sql(\n",
    "    \"select * from pointdf1, pointdf2 where ST_Distance(pointdf1.pointshape1,pointdf2.pointshape2) < 2\"\n",
    ")\n",
    "distance_join_df.explain()\n",
    "distance_join_df.show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Spatial Join - Range Join and RDD API Join\n",
    "\n",
    "Please refer to the example - airports per country: https://github.com/apache/sedona/blob/master/docs/usecases/ApacheSedonaSQL_SpatialJoin_AirportsPerCountry.ipynb"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Converting GeoPandas to Apache Sedona"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "gdf = gpd.read_file(\"data/gis_osm_pois_free_1.shp\")\n",
    "gdf = gdf.replace(pd.NA, \"\")\n",
    "osm_points = sedona.createDataFrame(gdf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- osm_id: string (nullable = true)\n",
      " |-- code: long (nullable = true)\n",
      " |-- fclass: string (nullable = true)\n",
      " |-- name: string (nullable = true)\n",
      " |-- geometry: geometry (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "osm_points.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Stage 15:>                                                         (0 + 1) / 1]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+----+---------+--------------+--------------------+\n",
      "|  osm_id|code|   fclass|          name|            geometry|\n",
      "+--------+----+---------+--------------+--------------------+\n",
      "|26860257|2422|camp_site|      de Kroon|POINT (15.3393145...|\n",
      "|26860294|2406|   chalet|Leśne Ustronie|POINT (14.8709625...|\n",
      "|29947493|2402|    motel|              |POINT (15.0946636...|\n",
      "|29947498|2602|      atm|              |POINT (15.0732014...|\n",
      "|29947499|2401|    hotel|              |POINT (15.0696777...|\n",
      "+--------+----+---------+--------------+--------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "osm_points.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "osm_points.createOrReplaceTempView(\"points\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "transformed_df = sedona.sql(\n",
    "    \"\"\"\n",
    "        SELECT osm_id,\n",
    "               code,\n",
    "               fclass,\n",
    "               name,\n",
    "               ST_Transform(geometry, 'epsg:4326', 'epsg:2180') as geom \n",
    "        FROM points\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Stage 16:>                                                         (0 + 1) / 1]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+----+---------+--------------+--------------------+\n",
      "|  osm_id|code|   fclass|          name|                geom|\n",
      "+--------+----+---------+--------------+--------------------+\n",
      "|26860257|2422|camp_site|      de Kroon|POINT (250776.778...|\n",
      "|26860294|2406|   chalet|Leśne Ustronie|POINT (221076.709...|\n",
      "|29947493|2402|    motel|              |POINT (233902.541...|\n",
      "|29947498|2602|      atm|              |POINT (232447.203...|\n",
      "|29947499|2401|    hotel|              |POINT (232208.377...|\n",
      "+--------+----+---------+--------------+--------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "transformed_df.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "transformed_df.createOrReplaceTempView(\"points_2180\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "neighbours_within_1000m = sedona.sql(\n",
    "    \"\"\"\n",
    "        SELECT a.osm_id AS id_1,\n",
    "               b.osm_id AS id_2,\n",
    "               a.geom \n",
    "        FROM points_2180 AS a, points_2180 AS b \n",
    "        WHERE ST_Distance(a.geom,b.geom) < 50\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "24/05/22 18:07:13 WARN JoinQuery: UseIndex is true, but no index exists. Will build index on the fly.\n",
      "[Stage 20:>                                                         (0 + 1) / 1]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+---------+--------------------+\n",
      "|      id_1|     id_2|                geom|\n",
      "+----------+---------+--------------------+\n",
      "|  26860257| 26860257|POINT (250776.778...|\n",
      "|  26860294| 26860294|POINT (221076.709...|\n",
      "|  29947493| 29947493|POINT (233902.541...|\n",
      "|3241834852| 29947493|POINT (233866.098...|\n",
      "|5964811085| 29947493|POINT (233861.172...|\n",
      "|5818905324| 29947498|POINT (232446.535...|\n",
      "|4165181885| 29947498|POINT (232449.441...|\n",
      "|5846858758| 29947498|POINT (232407.167...|\n",
      "|  29947498| 29947498|POINT (232447.203...|\n",
      "|  29947499| 29947499|POINT (232208.377...|\n",
      "|  30077461| 29947499|POINT (232185.872...|\n",
      "|  29947505| 29947505|POINT (228595.321...|\n",
      "|  29947499| 30077461|POINT (232208.377...|\n",
      "|  30077461| 30077461|POINT (232185.872...|\n",
      "|  30079117| 30079117|POINT (273599.241...|\n",
      "| 197624402|197624402|POINT (203703.035...|\n",
      "| 197663196|197663196|POINT (203936.327...|\n",
      "| 197953474|197953474|POINT (203724.746...|\n",
      "|1074233127|262310516|POINT (203524.110...|\n",
      "| 262310516|262310516|POINT (203507.730...|\n",
      "+----------+---------+--------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "neighbours_within_1000m.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Converting Apache Sedona to GeoPandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "24/05/22 18:07:17 WARN JoinQuery: UseIndex is true, but no index exists. Will build index on the fly.\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "df = neighbours_within_1000m.toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf = gpd.GeoDataFrame(df, geometry=\"geom\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_1</th>\n",
       "      <th>id_2</th>\n",
       "      <th>geom</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>26860257</td>\n",
       "      <td>26860257</td>\n",
       "      <td>POINT (250776.778 504581.332)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>26860294</td>\n",
       "      <td>26860294</td>\n",
       "      <td>POINT (221076.710 544222.650)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>29947493</td>\n",
       "      <td>29947493</td>\n",
       "      <td>POINT (233902.541 501298.382)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3241834852</td>\n",
       "      <td>29947493</td>\n",
       "      <td>POINT (233866.099 501323.801)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5964811085</td>\n",
       "      <td>29947493</td>\n",
       "      <td>POINT (233861.173 501326.441)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65670</th>\n",
       "      <td>6823696220</td>\n",
       "      <td>6823696220</td>\n",
       "      <td>POINT (234310.474 465790.364)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65671</th>\n",
       "      <td>6823721834</td>\n",
       "      <td>6823721834</td>\n",
       "      <td>POINT (234313.567 465869.023)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65672</th>\n",
       "      <td>6823721838</td>\n",
       "      <td>6823721838</td>\n",
       "      <td>POINT (234425.224 465829.994)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65673</th>\n",
       "      <td>6823772928</td>\n",
       "      <td>6823772928</td>\n",
       "      <td>POINT (234582.864 465875.142)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65674</th>\n",
       "      <td>6823823445</td>\n",
       "      <td>6823823445</td>\n",
       "      <td>POINT (236859.419 428547.388)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>65675 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             id_1        id_2                           geom\n",
       "0        26860257    26860257  POINT (250776.778 504581.332)\n",
       "1        26860294    26860294  POINT (221076.710 544222.650)\n",
       "2        29947493    29947493  POINT (233902.541 501298.382)\n",
       "3      3241834852    29947493  POINT (233866.099 501323.801)\n",
       "4      5964811085    29947493  POINT (233861.173 501326.441)\n",
       "...           ...         ...                            ...\n",
       "65670  6823696220  6823696220  POINT (234310.474 465790.364)\n",
       "65671  6823721834  6823721834  POINT (234313.567 465869.023)\n",
       "65672  6823721838  6823721838  POINT (234425.224 465829.994)\n",
       "65673  6823772928  6823772928  POINT (234582.864 465875.142)\n",
       "65674  6823823445  6823823445  POINT (236859.419 428547.388)\n",
       "\n",
       "[65675 rows x 3 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
